package com.cms.biz.queryAnalyse.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;
import com.pub.util.StringFunction;


/**
 * <p>Title: 项目查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPSCTZBMWQRQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;
	
	protected     String                   msTableName = "";
	
	protected     String                  msTableName1 = "";
	
	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		EFRowSet     queryRowset = EFRowSet.getInstance();
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			
			// 获取查询数据
			queryDataset = queryDataObject(stmt, PO);
			queryRowset.setExtProperty(EMPQueryParamEnum.PAGEQUERY, PO.getValue(EMPQueryParamEnum.PAGEQUERY, null));
			queryRowset.setDataSet(EMPQueryParamEnum.QUERYRESULT, queryDataset);
			// 封装返回数据
		   	RO = new JResponseObject(queryRowset, 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
			try {
				stmt.executeUpdate("drop table "+msTableName);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 组织数据的过程
	 * @throws Exception
	 * @return TreeTableDataManager
	 */
	private EFDataSet queryDataObject(Statement stmt, JParamObject po) throws Exception{
		EFDataSet   queryDataset = null;
		//创建临时表
	    createTempTable(stmt);
	    
	    //取出原始数据
	    getOriginData(stmt, po);
	    
	    //创建临时表
	    queryDataset = convertDataSet(stmt, po);
		return queryDataset;
	}
	
	/**
	 * 创建临时表
	 * @throws Exception
	 */
	private void createTempTable(Statement stmt) throws Exception{
	    msTableName = "SCTZBMWQR"+StringFunction.getTempStr(6);
	    String        strSql = "";
	    
	    String tempTableBody = " F_ID          varchar(1)           default ''   null,"
	         				 + " F_KJQJ        varchar(255)         default ''   null,"
	         				 + " F_DJBH        varchar(255)         default ''   null,"
	         				 + " F_GUID        varchar(255)         default ''   null,"	         				
	         				 + " F_HTBH        varchar(255)         default ''   null,"
	         				 + " F_HTMC        varchar(255)         default ''   null,"
	         				 + " F_XMBH        varchar(255)	        default ''   null,"
	         				 + " F_XMMC        varchar(255)   	    default ''   null,"
	         				 + " F_FLBH        varchar(255)         default ''   null,"
	         				 + " F_BMBH        varchar(255)         default ''   null,"
	         				 + " F_BMMC        varchar(255)         default ''   null,"
	         				 + " F_SCNR        varchar(255)   	    default ''   null,"
	         				 + " F_QSRQ           datetime   	    default null null,"
	         				 + " F_JZRQ           datetime   	    default null null,"
	         				 + " F_QRZT        varchar(255)   	    default ''   null";
	    strSql = "create table " + msTableName + "(" + tempTableBody + ")";
	    stmt.execute(strSql);
	  }
	  
	/**
	 * 逐级汇总数据
	 * @throws Exception
	 */
	public void getOriginData(Statement stmt, JParamObject po) throws Exception {
		String              strSql = "";
		String             NODE_ID = "";
		ResultSet               rs = null;
		String              F_QRZT = po.GetValueByParamName("F_QRZT", "");
		EFDataSet           nodeDS = EFDataSet.getInstance();
		EFRowSet            nodeRS = null;
		//第二个会签节点是确认节点
		strSql = "select * from FLOW_NODE_LIST where FLOW_ID = 'SCTZ_FLOW' and NODE_TYPE = 'countersign'";
		rs = stmt.executeQuery(strSql);
		DataSetUtils.resultSet2DataSet(rs, nodeDS);
		
		nodeRS = nodeDS.getRowSet(1);
		NODE_ID = nodeRS.getString("NODE_ID", "");
		
		if(F_QRZT.equals("0")) {
			insertBMWQRXX(stmt, po, NODE_ID);
			insertBMYQRXX(stmt, po, NODE_ID);
		} else if(F_QRZT.equals("1")){
			insertBMWQRXX(stmt, po, NODE_ID);
		} else if(F_QRZT.equals("2")){
			insertBMYQRXX(stmt, po, NODE_ID);
		}
	}
	
	public void insertBMWQRXX(Statement stmt, JParamObject po, String NODE_ID) throws Exception {
		String              strSql = "";
		String              F_QSQJ = po.GetValueByParamName("F_QSRQ", "").replaceAll("-", "");
		String              F_JZQJ = po.GetValueByParamName("F_JZRQ", "").replaceAll("-", "");
		String              F_HTBH = po.GetValueByParamName("F_HTBH", "");
		String              F_XMBH = po.GetValueByParamName("F_XMBH", "");
		String              F_BMBH = po.GetValueByParamName("F_BMBH", "");
		String               F_TXM = po.GetValueByParamName("F_TXM", "");
		
		strSql = " insert into " + msTableName + " (F_ID, F_KJQJ, F_DJBH, F_GUID, F_FLBH, F_BMBH, F_SCNR, F_QSRQ, F_JZRQ, F_QRZT)"
		       + " select '0', SCTZGQ.F_KJQJ, SCTZGQ.F_DJBH, SCTZGQ.F_GUID, SCTZGQ.F_FLBH,SCTZGQ.F_BMBH, "
		       + " SCTZGQ.F_SCNR, SCTZGQ.F_QSRQ, SCTZGQ.F_JZRQ, '1'"
		       + " from SCTZGQ"
		       + " where SCTZGQ.F_KJQJ <= '" + F_JZQJ + "' and SCTZGQ.F_KJQJ >= '" + F_QSQJ + "'";
		if(!F_TXM.equals("")) {
			strSql += " and SCTZGQ.F_KJQJ = '" + F_TXM.substring(0, 6) + "' and SCTZGQ.F_DJBH = '" + F_TXM.substring(6) + "'";
		}
		if(!F_BMBH.trim().equals("")) {
			strSql += " and SCTZGQ.F_BMBH = '" + F_BMBH + "' ";
		}
		strSql += " and exists(select 1 from SCTZXX where SCTZXX.F_KJQJ = SCTZGQ.F_KJQJ and SCTZXX.F_DJBH = SCTZGQ.F_DJBH and SCTZXX.F_GUID = SCTZGQ.F_GUID and " 
		        + " SCTZXX.F_XMBH like '" + F_HTBH + "%' and SCTZXX.F_HTBH like '" + F_XMBH + "%')"
		        + " and exists(select 1 from FLOW_TASK_LIST where FLOW_TASK_LIST.FLOW_ID = 'SCTZ_FLOW' and FLOW_TASK_LIST.BIZ_DJBH = SCTZGQ.F_DJBH and " 
				+ " FLOW_TASK_LIST.BIZ_DATE = SCTZGQ.F_KJQJ and FLOW_TASK_LIST.BIZ_GUID = SCTZGQ.F_GUID and FLOW_TASK_LIST.TASK_TO_UNIT = SCTZGQ.F_BMBH and "
				+ " RESR_STATUS = 'pending' and NODE_TAG = '" + NODE_ID + "')"
				+ " order by SCTZGQ.F_DJBH, SCTZGQ.F_FLBH";
		stmt.execute(strSql);
	}
	
	public void insertBMYQRXX(Statement stmt, JParamObject po, String NODE_ID) throws SQLException {
		String              strSql = "";
		String              F_QSQJ = po.GetValueByParamName("F_QSRQ", "").replaceAll("-", "");
		String              F_JZQJ = po.GetValueByParamName("F_JZRQ", "").replaceAll("-", "");
		String              F_HTBH = po.GetValueByParamName("F_HTBH", "");
		String              F_XMBH = po.GetValueByParamName("F_XMBH", "");
		String              F_BMBH = po.GetValueByParamName("F_BMBH", "");
		String               F_TXM = po.GetValueByParamName("F_TXM", "");
		
		strSql = " insert into " + msTableName + " (F_ID, F_KJQJ, F_DJBH, F_GUID, F_FLBH, F_BMBH, F_SCNR, F_QSRQ, F_JZRQ, F_QRZT)"
		       + " select '0', SCTZGQ.F_KJQJ, SCTZGQ.F_DJBH, SCTZGQ.F_GUID, SCTZGQ.F_FLBH,SCTZGQ.F_BMBH, "
		       + " SCTZGQ.F_SCNR, SCTZGQ.F_QSRQ, SCTZGQ.F_JZRQ, '2'"
		       + " from SCTZGQ"
		       + " where SCTZGQ.F_KJQJ <= '" + F_JZQJ + "' and SCTZGQ.F_KJQJ >= '" + F_QSQJ + "'";
		if(!F_TXM.equals("")) {
			strSql += " and SCTZGQ.F_KJQJ = '" + F_TXM.substring(0, 6) + "' and SCTZGQ.F_DJBH = '" + F_TXM.substring(6) + "'";
		}
		if(!F_BMBH.trim().equals("")) {
			strSql += " and SCTZGQ.F_BMBH = '" + F_BMBH + "' ";
		}
		strSql += " and exists(select 1 from SCTZXX where SCTZXX.F_KJQJ = SCTZGQ.F_KJQJ and SCTZXX.F_DJBH = SCTZGQ.F_DJBH and SCTZXX.F_GUID = SCTZGQ.F_GUID and " 
			    + " SCTZXX.F_XMBH like '" + F_HTBH + "%' and SCTZXX.F_HTBH like '" + F_XMBH + "%')"
			    + " and exists(select 1 from FLOW_TASK_LIST where FLOW_TASK_LIST.FLOW_ID = 'SCTZ_FLOW' and FLOW_TASK_LIST.BIZ_DJBH = SCTZGQ.F_DJBH and " 
			    + " FLOW_TASK_LIST.BIZ_DATE = SCTZGQ.F_KJQJ and FLOW_TASK_LIST.BIZ_GUID = SCTZGQ.F_GUID and FLOW_TASK_LIST.F_CHAR19 = SCTZGQ.F_BMBH and "
			    + " RESR_STATUS = 'waiting' and NODE_SRC = '" + NODE_ID + "')"
		        + " order by SCTZGQ.F_DJBH, SCTZGQ.F_FLBH";
		stmt.execute(strSql);
	}
	
	public EFDataSet convertDataSet(Statement stmt, JParamObject po) throws Exception {
		EFDataSet queryDataset = null;
		EFDataSet   newDataset = EFDataSet.getInstance();
		ResultSet           rs = null;
		String          strSql = ""; 
		EMPPageQuery pageQuery = (EMPPageQuery) po.getValue(EMPQueryParamEnum.PAGEQUERY, "");

		//更新合同、项目信息
		strSql = " update " + msTableName + " TEMP"
	           + " INNER JOIN SCTZXX TEMP1 ON TEMP.F_KJQJ = TEMP1.F_KJQJ and TEMP.F_DJBH = TEMP1.F_DJBH and TEMP.F_GUID = TEMP1.F_GUID " 
	           + " set TEMP.F_XMBH = TEMP1.F_XMBH, TEMP.F_HTBH = TEMP1.F_HTBH" ;
		stmt.execute(strSql);
		
		//更新项目名称
		strSql = " update " + msTableName + " TEMP"
	           + " INNER JOIN HTXMZD TEMP1 ON TEMP.F_XMBH = TEMP1.F_XMBH" 
	           + " set TEMP.F_XMMC = TEMP1.F_XMMC" ;
		stmt.execute(strSql);
		
		//更新合同名称
		strSql = " update " + msTableName + " TEMP"
        	   + " INNER JOIN HTXXZD TEMP1 ON TEMP.F_HTBH = TEMP1.F_HTBH" 
        	   + " set TEMP.F_HTMC = TEMP1.F_HTMC" ;
		stmt.execute(strSql);
	
		//更新部门名称
		strSql = " update " + msTableName + " TEMP"
        	   + " INNER JOIN HYBMZD TEMP1 ON TEMP.F_BMBH = TEMP1.F_BMBH" 
        	   + " set TEMP.F_BMMC = TEMP1.F_BMMC" ;
		stmt.execute(strSql);
		
		strSql = " select * from " + msTableName + " order by F_KJQJ desc, F_DJBH desc, F_BMBH";
		rs = stmt.executeQuery(strSql);
		queryDataset = DataSetUtils.resultSet2DataSet(rs, queryDataset);

		if(pageQuery == null) return queryDataset;
		
		pageQuery.setTotalCount(queryDataset.getRowCount());
		newDataset = EMPLimitConvertUtil.getDataSetLimit(queryDataset, pageQuery.getPageNum(), pageQuery.getNumPerPage());
		return newDataset;
	}
}
